package com.elitel.frame.base.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.elitel.common.core.domain.JsonResult;
import com.elitel.common.mongodb.MongoDBUtil;
import com.elitel.common.mongodb.QueryInfo;
import com.elitel.frame.base.component.RedisCacheComponent;
import com.elitel.frame.base.component.RedisCacheDataBase;
import com.elitel.frame.base.component.exportprojectdata.DataBaseConnInfo;
import com.elitel.frame.base.service.*;
import com.elitel.frame.business.service.DataBaseMetadataService;
import com.elitel.frame.main.entity.*;
import com.elitel.frame.main.entity.vo.KeyValueModelVo;
import com.elitel.frame.main.entity.vo.PageResponseVo;
import com.elitel.frame.main.entity.vo.ZTreeDataModelVo;
import com.mongodb.BasicDBObject;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author guoyanfei @Description 元数据表配置服务
 */
@Controller
@RequestMapping("/system/dataConfig")
public class DataConfigController {
    private static final Logger logger = LoggerFactory.getLogger(DataConfigController.class);
    private final SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static int columnNum = 12;
    @Autowired
    private CfgTableService cfgTableService;
    @Autowired
    private DataBaseMetadataService dataBaseMetadataService;
    @Autowired
    private CfgDbService cfgDbService;
    @Autowired
    private CfgFieldService cfgFieldService;
    @Autowired
    private BaseFieldconfigService baseFieldconfigService;
    @Autowired
    private RedisCacheComponent redisCacheComponent;
    @Autowired
    private RedisCacheDataBase redisCacheDataBase;
    @Autowired
    private BaseFieldenumService baseFieldenumService;
    @Autowired
    private DataBaseConnInfo dataBaseConnInfo;
    @Autowired(required = false)
    private KuduService kuduService;

    public static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param
     * @return List<ZTreeDataModelVo>(ZTree数据格式集合)
     * @throws @Title: getTreeList @Description: TODO(获取树形元数据列表集合)
     * @author guoyanfei
     */
    @RequestMapping("/treeList")
    @ResponseBody
    public List<ZTreeDataModelVo> getTreeList(
            HttpServletRequest request, HttpServletResponse response) {
        List<ZTreeDataModelVo> zTreeDataModelVos = new ArrayList<ZTreeDataModelVo>();
        try {
            zTreeDataModelVos = redisCacheDataBase.getTreeListRedisCache();
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return zTreeDataModelVos;
    }

    /**
     * @return List<Map < String, String>>集合
     * @throws @Title: getTableList @Description: TODO(通过数据库dbkey查询表名称和描述集合)
     * @author guoyanfei
     */
    @RequestMapping("/tableList")
    @ResponseBody
    public List<Map<String, String>> getTableList(HttpServletRequest request, HttpServletResponse response) {
        List<Map<String, String>> maps = new ArrayList<Map<String, String>>();
        try {
            String dbkey = request.getParameter("dbkey");
            if (StringUtils.isNotBlank(dbkey)) {
                CfgDb cfgDb = cfgDbService.selectByKey(dbkey);
                List<Map<String, String>> maplist = dataBaseMetadataService.getAllTableByDataSource(cfgDb);
                maps = mapKeysToUpperCase(maplist);
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }
        return maps;
    }

    private List<Map<String, String>> mapKeysToUpperCase(List<Map<String, String>> mapList) {
        List<Map<String, String>> returnList = new ArrayList<>(mapList.size());
        mapList.forEach(oneMap -> {
            Map<String, String> returnMap = new HashMap<>(oneMap.size());
            // 转为小写 oneMap.forEach((key, val) -> returnMap.put(key.toLowerCase(), val));
            oneMap.forEach((key, val) -> returnMap.put(key.toUpperCase(), val));
            returnList.add(returnMap);
        });
        return returnList;
    }

    private List<Map<String, Object>> mapKeysToUpperCaseObject(List<Map<String, Object>> mapList) {
        List<Map<String, Object>> returnList = new ArrayList<>(mapList.size());
        mapList.forEach(oneMap -> {
            Map<String, Object> returnMap = new HashMap<>(oneMap.size());
            // 转为小写 oneMap.forEach((key, val) -> returnMap.put(key.toLowerCase(), val));
            oneMap.forEach((key, val) -> returnMap.put(key.toUpperCase(), val));
            returnList.add(returnMap);
        });
        return returnList;
    }

    /**
     * @return 主键字段
     * @throws @Title: getPrimaryKeyByTableName @Description: TODO(通过表名称获取该表的主键字段)
     * @author guoyanfei
     */
    @RequestMapping("/parmaryKey")
    @ResponseBody
    public String getPrimaryKeyByTableName(HttpServletRequest request) {
        String primaryName = "";
        try {
            String dtname = request.getParameter("dtname");
            String dbkey = request.getParameter("dbkey");
            CfgDb cfgDb = cfgDbService.selectByKey(dbkey);
            primaryName = dataBaseMetadataService.getPkName(cfgDb, dtname);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }
        return primaryName;
    }

    /**
     * @return 查询结果
     * @throws @Title: getdtconfigIs @Description: TODO(查询表标识在数据库中是否存在)
     * @author guoyanfei
     */
    @RequestMapping("/getdtConfigId")
    @ResponseBody
    public String getdtconfigIs(HttpServletRequest request, HttpServletResponse response) {
        String result = "";
        try {
            String dtConfigId = request.getParameter("dtConfigId");
            result = cfgTableService.getdtConfigIdIs(dtConfigId);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }

        return result;
    }

    /**
     * @return 插入结果
     * @throws @Title: adddtTable @Description: TODO(新增表配置信息)
     * @author guoyanfei
     */
    @RequestMapping("/add")
    @ResponseBody
    public int adddtTable(
            @RequestBody CfgTable cfgTable, HttpServletRequest request, HttpServletResponse response) {
        int result = 0;
        try {
            Date datenow = new Date();
            cfgTable.setGuid(UUID.randomUUID().toString());
            cfgTable.setCreatedate(df.format(datenow));
            cfgTable.setDtTabletype(1);
            CfgDb cfgDb = cfgDbService.selectByKey(cfgTable.getDtConn());
            cfgTable.setDbName(cfgDb.getDbName());
            String dtName = cfgTable.getDtName();

            Map<String, String> infoByModuleDtName = cfgTableService.getInfoByModuleDtName(1, cfgDb.getDbName(), dtName);
            if (infoByModuleDtName != null) {
                cfgTable.setTypeId(infoByModuleDtName.get("typeid"));
            }
            if (StringUtils.isBlank(cfgTable.getDtNameCn())) {
                cfgTable.setDtNameCn(dtName);
            }
            result = cfgTableService.addcfgTable(cfgTable);
            if (result > 0) {
                redisCacheDataBase.refreshTreelistRedisCache();
            }

            //刷新表数据量统计
            new Thread() {//1.继承Thread类
                public void run() {
                    try {
                        List<Map<String, Object>> dbTableRecordCount = dataBaseMetadataService.getDBTableRecordCount(cfgDb, dtName);
                        cfgTableService.refreshTableCount(cfgDb.getDbKey(), dbTableRecordCount);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }.start();//开启线程
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }

        return result;
    }

    /**
     * 一次性装载数据库中的表信息如服务
     *
     * @param request
     * @return
     */
    @RequestMapping("/loadTable")
    @ResponseBody
    public JSONObject loadtTable(HttpServletRequest request) {
        long starttime = System.currentTimeMillis();
        JSONObject json = new JSONObject();
        String dbkey = request.getParameter("dbkey");
        String moduleMark = request.getParameter("moduleMark");

        CfgDb cfgDb = cfgDbService.selectByKey(dbkey);
        if (cfgDb == null) {
            return json;
        }
        List<Map<String, String>> maplist = dataBaseMetadataService.getAllTableByDataSource(cfgDb);

        //获取该dbKey下已配置的表，如果已添加，则跳过
        List<CfgTable> alreadyCfgTableList = cfgTableService.getListbyConn(dbkey);
        maplist = maplist.stream()
                .filter(table -> alreadyCfgTableList.stream()
                        .noneMatch(o -> o.getDtName().equals(table.get("table_name"))))
                .collect(Collectors.toList());

        if (!maplist.isEmpty()) {
            List<String> tableNameList = maplist.stream().map(o -> o.get("table_name")).collect(Collectors.toList());
            Map<String, String> pkNameMap = dataBaseMetadataService.getPkNameMap(cfgDb, tableNameList);
            Map<String, List<KeyValueModelVo>> allFieldMap = dataBaseMetadataService.getAllFieldMap(cfgDb, tableNameList);

            cfgTableService.loadTableInfo(cfgDb, maplist, moduleMark, pkNameMap, allFieldMap);
        }

        //刷新表数据量统计
        new Thread() {//1.继承Thread类
            public void run() {
                try {
                    List<Map<String, Object>> dbTableRecordCountList = dataBaseMetadataService.getDBTableRecordCount(cfgDb, null);
                    cfgTableService.refreshTableCount(cfgDb.getDbKey(), dbTableRecordCountList);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }.start();//开启线程

        double usetime = System.currentTimeMillis() - starttime;
        json.put("time", usetime / 1000);
        json.put("state", 1);
        return json;
    }

//    @RequestMapping("/loadTable")
//    @ResponseBody
//    public JSONObject loadtTable(HttpServletRequest request) {
//        long starttime = System.currentTimeMillis();
//        JSONObject json = new JSONObject();
//        String dbkey = request.getParameter("dbkey");
//
//        CfgDb cfgDb = cfgDbService.selectByKey(dbkey);
//        if (cfgDb == null) {
//            return json;
//        }
//        List<Map<String, String>> maplist = new ArrayList<Map<String, String>>();
//        // 获取该数据库下所有表的tablename和comment信息,map<k,v>形式
//        try {
//                maplist = dataBaseMetadataService.getAllTableByDataSource(cfgDb);
//                maplist = mapKeysToUpperCase(maplist);
//        } catch (Exception e) {
//            e.printStackTrace();
//            logger.error(e.getMessage(), e);
//        }
//
//        HashSet<String> dtConfigIds = new HashSet<>();
//        HashMap<String, CfgTable> dtidAndcfTable = new HashMap<>();
//        CfgTable table = new CfgTable();
//        // 遍历集合,取出各个表的信息
//        for (Map<String, String> map : maplist) {
//            String primaryName = "";
//            String dtname = map.get("TABLE_NAME");
//            String dtConfigId = dbkey + "_" + dtname;
//            dtConfigIds.add(dtConfigId);
//            CfgTable cft = cfgTableService.getInfoByDtconfigId(dtConfigId);
//            if (cft == null) {
//                dtidAndcfTable.put(dtConfigId, cft);
//                String dtOrder = "";
//                try {
//                    // 获取该表主键primarykey
//                    primaryName = dataBaseMetadataService.getPkName(cfgDb, dtname);
//                    dtOrder = primaryName;
//                    //若主键不存在，则取第一个字段作为排序字段
//                    if (StringUtils.isBlank(primaryName)) {
//                        List<KeyValueModelVo> listFields = dataBaseMetadataService.getAllFieldByTableName(cfgDb, dtname);
//                        if (listFields.size() > 0) {
//                            KeyValueModelVo keyValueModelVo = listFields.get(0);
//                            dtOrder = keyValueModelVo.getKey();
//                        }
//                    }
//                } catch (Exception e) {
//                    e.printStackTrace();
//                    logger.error(e.getMessage(), e);
//                }
//                // 表属性赋值
//
//                String dtNameCn = map.get("COMMENTS");
//                String dtRemark = map.get("COMMENTS");
//
//                CfgTable cfgTable = new CfgTable();
//                cfgTable.setDtName(dtname);
//                cfgTable.setDtConfigId(dtConfigId);
//                cfgTable.setDtNameCn(dtNameCn);
//                cfgTable.setDtOrder(dtOrder);
//                cfgTable.setDtConn(dbkey);
//                cfgTable.setDtPrimarykey(primaryName);
//                cfgTable.setDtRemark(dtRemark);
//                cfgTable.setGuid(UUID.randomUUID().toString());
//                cfgTable.setCreatedate(df.format(new Date()));
//                cfgTable.setDtTabletype(1);
//
//                cfgTable.setDbName(cfgDb.getDbName());
//                cfgTable.setDataQuantity(0);
//                Map<String, String> infoByModuleDtName = cfgTableService.getInfoByModuleDtName(1, cfgDb.getDbName(), dtname);
//                if (infoByModuleDtName != null) {
//                    cfgTable.setTypeId(infoByModuleDtName.get("typeid"));
//                    cfgTable.setDtNameCn(infoByModuleDtName.get("dt_cnname"));
//                }
//
//                cfgTableService.addcfgTable(cfgTable);
//            } else {
//                dtidAndcfTable.put(dtConfigId, table);
//            }
//        }
//
//        for (String dtConfigId : dtConfigIds) {
//            List<KeyValueModelVo> keyValueModelVos = getNewFieldByTableName(dtConfigId);
//            String strFields = "";
//            for (KeyValueModelVo keyValueModelVo : keyValueModelVos) {
//                strFields +=
//                        keyValueModelVo.getKey()
//                                + "-"
//                                + keyValueModelVo.getValue()
//                                + "-"
//                                + keyValueModelVo.getDataType()
//                                + "|";
//            }
//
//            if (!StringUtils.isEmpty(strFields)) {
//                strFields = strFields.substring(0, strFields.length() - 1);
//                if (dtidAndcfTable.get(dtConfigId) != null) {
//                    batchField(dtConfigId, strFields, "update", false);
//                } else {
//                    batchField(dtConfigId, strFields, "add", false);
//                }
//            }
//        }
//
//        final String dbKey = cfgDb.getDbKey();
//        new Thread() {//1.继承Thread类
//            public void run() {
//                try {
//                    //刷新cfgtable中的数据条数
//                    if ("KUDU".equalsIgnoreCase(cfgDb.getDbVersion())) {
//                        kuduService.refreshKuduTableCount(dbKey);
//                    }
//                } catch (Exception e) {
//                    e.printStackTrace();
//                }
//            }
//        }.start();//开启线程
//
//        double usetime = System.currentTimeMillis() - starttime;
//        json.put("time", usetime / 1000);
//        json.put("state", 1);
//        return json;
//    }

    /**
     * 导出该数据库服务配置的表信息到excel
     *
     * @param request
     * @return
     */
    @RequestMapping("/exportTable")
    @ResponseBody
    public int exportTable(HttpServletRequest request, HttpServletResponse response) {
        int result = 0;
        String dbkey = request.getParameter("dbkey");
        String name = cfgDbService.selectByKey(dbkey).getDbName();
        String dbName = StringUtils.isEmpty(name) ? " " : name; // 数据库名称
        List<String> rowlist = new ArrayList<>();
        rowlist.add("数据库标识---数据库名称---表服务标识---表标识---表名称---主键---字段标识---字段名称---字段排序---字段类型---是否主键---备注");

        // 获取该dbkey下的tablelist
        List<CfgTable> tableList = cfgTableService.getListbyConn(dbkey);
        for (int j = 0; j < tableList.size(); j++) {
            CfgTable table = tableList.get(j);
            String dtConn = StringUtils.isEmpty(table.getDtConn()) ? " " : table.getDtConn(); // 数据库标识
            String dtConfigId =
                    StringUtils.isEmpty(table.getDtConfigId()) ? " " : table.getDtConfigId(); // 表标识
            if (dtConfigId.contains("_VIEW")) {
                continue;
            }
            String dtName = StringUtils.isEmpty(table.getDtName()) ? " " : table.getDtName(); // 表名
            String dtNameCn =
                    StringUtils.isEmpty(table.getDtNameCn()) ? " " : table.getDtNameCn(); // 表中文名称
            String dtPrimarykey =
                    StringUtils.isEmpty(table.getDtPrimarykey()) ? " " : table.getDtPrimarykey();

            List<CfgField> fieldlist = cfgFieldService.getAllField(dtConfigId);
            for (CfgField field : fieldlist) {
                String dfName = StringUtils.isEmpty(field.getDfName()) ? " " : field.getDfName(); // 字段名
                String dfNameCn =
                        StringUtils.isEmpty(field.getDfNameCn()) ? " " : field.getDfNameCn(); // 字段中文名称
                String dfOrder =
                        StringUtils.isEmpty(field.getDfOrder().toString())
                                ? " "
                                : field.getDfOrder().toString(); // 字段排序
                String dfRemark = StringUtils.isEmpty(field.getRemark()) ? " " : field.getRemark(); // 备注
                String dfType = StringUtils.isEmpty(field.getDtType()) ? " " : field.getDtType(); // 字段类型
                String dfIsprimarykey = " ";
                Integer isprimarykey = field.getDfIsprimarykey();
                if (isprimarykey == 1) {
                    dfIsprimarykey = "是";
                } else if (isprimarykey == 0) {
                    dfIsprimarykey = "否";
                }
                String row =
                        dtConn
                                + "---"
                                + dbName
                                + "---"
                                + dtConfigId
                                + "---"
                                + dtName
                                + "---"
                                + dtNameCn
                                + "---"
                                + dtPrimarykey
                                + "---"
                                + dfName
                                + "---"
                                + dfNameCn
                                + "---"
                                + dfOrder
                                + "---"
                                + dfType
                                + "---"
                                + dfIsprimarykey
                                + "---"
                                + dfRemark;
                rowlist.add(row);
            }
        }
        response.setContentType("application/ms-excel;charset=UTF-8");
        OutputStream out = null;
        try {
            response.setHeader(
                    "Content-Disposition",
                    "attachment;filename="
                            .concat(String.valueOf(URLEncoder.encode(dbName + ".xlsx", "UTF-8"))));
            out = response.getOutputStream();
            Workbook wb = writeExcel(rowlist);
            wb.write(out); // 将数据写出去
            result = 1;
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                out.flush();
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 从excel导入该数据库服务配置的表信息
     *
     * @param request
     * @return
     */
    @RequestMapping("/importTable")
    @ResponseBody
    public JSONObject importTable(
            @RequestParam("file") MultipartFile file,
            @RequestParam("dbkey") String dbkey,
            HttpServletRequest request) {
        JSONObject json = new JSONObject();
        long starttime = System.currentTimeMillis();
        int result = 0;
        String dfStr = "";
        String oldName = "";
        String dtid = "";
        int oldsize = 1;
        int count = 0;
        ArrayList<String> dts = new ArrayList<>();
        HashMap<String, String> dtIdAndFields = new HashMap<>();
        HashMap<String, String> dtNameAndCn = new HashMap<>();
        try {
            // 获取源数据库表 列表以进行表校验
            CfgDb cfgDb = cfgDbService.selectByKey(dbkey);
            List<Map<String, String>> tablelist = new ArrayList<>();
            if (cfgDb != null) {
                tablelist = dataBaseMetadataService.getAllTableByDataSource(cfgDb);
            }

            HashSet<String> tableset = new HashSet<>();
            List<KeyValueModelVo> dtfieldlist = new ArrayList<>();
            // 获取excel数据
            String name = file.getOriginalFilename();

            File infile = null;
            List<String> rows = new ArrayList<>();
            String substring = name.substring(name.lastIndexOf(".") + 1);
            if (file.equals("") || file.getSize() <= 0 || !substring.contains("xls")) {
                json.put("state", -1);
                return json;
            } else {
                InputStream ins = file.getInputStream();
                infile = new File(file.getOriginalFilename());
                inputStreamToFile(ins, infile);
                rows = readExcel(infile);

                // 删除本地临时文件
                if (infile != null) {
                    File localFile = new File(infile.toURI());
                    localFile.delete();
                }
            }
            for (String row : rows) {
                boolean isimporttable = false;
                boolean isSelectFields = false;
                String[] fields = row.split("---");
                String dtName = fields[3];
                String dtConfigId = dbkey + "_" + dtName;

                // 检验表，如果源库中没有该表，则忽略导入
                if (!tableset.contains(dtName)) {
                    for (Map<String, String> table : tablelist) {
                        String dtname = table.get("TABLE_NAME");
                        if (dtName.equals(dtname)) {
                            dtid = dtConfigId;
                            isimporttable = true;
                            isSelectFields = true;
                            dtfieldlist.clear();
                            tableset.add(dtName);
                            break;
                        }
                    }
                } else {
                    isimporttable = true;
                }
                if (isimporttable) {
                    String dtNameCn = fields[4];
                    dtNameAndCn.put(dtName, dtNameCn);

                    // 获取表标识表名主键等表唯一信息，去重有序存入dts列表
                    if (!dts.contains(dtName)) {
                        dts.add(dtName);
                    } else {
                        oldName = dtName;
                        count++;
                    }

                    // 如果有下一个新表加入，则把上一个表的字段信息刷写到dtIdAndFields<dtConfigid,fields> map集合
                    int newsize = dts.size();
                    if (newsize > oldsize) {
                        oldsize = newsize;
                        if (count != 0) {
                            if (!StringUtils.isEmpty(dfStr)) {
                                dtIdAndFields.put(dbkey + "_" + oldName, dfStr.substring(0, dfStr.length() - 1));
                            }
                            count = 0;
                        } else if (!StringUtils.isEmpty(dfStr)) {
                            String dtname = dts.get(dts.size() - 2);
                            dtIdAndFields.put(dbkey + "_" + dtname, dfStr.substring(0, dfStr.length() - 1));
                        }
                        dfStr = "";
                    }

                    String dfName = fields[6];
                    // 获取该表下的所有字段信息以进行字段校验
                    if (isSelectFields) {
                        dtfieldlist = dataBaseMetadataService.getAllFieldByTableName(cfgDb, dtName);
                    }
                    boolean isaddfield = false;
                    String dfType = "";

                    // 校验表字段，如果源库表不存在该字段，则忽略导入
                    for (KeyValueModelVo dbfield : dtfieldlist) {
                        if (dbfield.getKey().equals(dfName)) {
                            isaddfield = true;
                            dfType = dbfield.getDataType();
                            break;
                        }
                    }

                    if (isaddfield) {
                        String dfNameCn = fields[7];
                        // 主要字段信息df
                        dfStr += dfName + "-" + dfNameCn + "-" + dfType + "|";
                    }
                }
            }
            // 遍历结束，刷写最后一轮表字段信息
            if (!StringUtils.isEmpty(dfStr)) {
                dtIdAndFields.put(dtid, dfStr.substring(0, dfStr.length() - 1));
            }

            // 导入（更新）表信息
            HashMap<String, CfgTable> map = new HashMap<>();
            for (String dtName : dts) {
                String tid = dbkey + "_" + dtName;
                CfgTable cfgTable = new CfgTable();
                cfgTable.setDtName(dtName);
                cfgTable.setDtConfigId(tid);
                cfgTable.setDtNameCn(dtNameAndCn.get(dtName));
                String dtPrimarykey = dataBaseMetadataService.getPkName(cfgDb, dtName);
                String primarykey = StringUtils.isEmpty(dtPrimarykey) ? " " : dtPrimarykey;
                cfgTable.setDtOrder(primarykey);
                cfgTable.setDtConn(dbkey);
                cfgTable.setDtPrimarykey(primarykey);
                cfgTable.setDtRemark(dtNameAndCn.get(dtName));
                cfgTable.setGuid(UUID.randomUUID().toString());
                Date datenow = new Date();
//                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                cfgTable.setCreatedate(df.format(datenow));
                cfgTable.setDtTabletype(1);

                // 校验服务中是否已存在该表信息
                CfgTable cft = cfgTableService.getInfoByDtconfigId(tid);
                map.put(tid, cft);
                if (cft != null) { // 存在则更新表信息
                    cfgTable.setGuid(cft.getGuid());
                    cfgTableService.updatecfgTable(cfgTable);
                } else { // 否则新增该表信息
                    cfgTableService.addcfgTable(cfgTable);
                }
            }

            // 导入（更新）字段信息
            for (String dfConfigid : dtIdAndFields.keySet()) {
                String strFields = dtIdAndFields.get(dfConfigid);
                if (!StringUtils.isEmpty(strFields)) {
                    // 若该表已存在，更新字段信息 ：dfName + "-" + dfNameCn + "-" + dfType
                    if (map.get(dfConfigid) != null) {
                        batchField(dfConfigid, strFields, "update", true);
                    } else { // 否则（新表）增加字段信息
                        batchField(dfConfigid, strFields, "add", true);
                    }
                }
            }
            result = 1;
        } catch (Exception e) {
            e.printStackTrace();
            result = 0;
        }

        double usetime = System.currentTimeMillis() - starttime;
        json.put("time", usetime / 1000);
        json.put("state", result);
        return json;
    }

    /**
     * 读取Excel数据
     *
     * @param file
     * @return
     */
    public List<String> readExcel(File file) {
        List<String> rows = new ArrayList<>();
        try {
            Workbook workBook = getWorkbook(file); // 从文件流中获取Excel工作区对象（WorkBook）
            Sheet sheet = workBook.getSheetAt(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) { // 循环读取Excel表中的内容
                Row row = sheet.getRow(i);

                StringBuilder sb = new StringBuilder();
                if (row != null) {
                    for (int j = 0; j < columnNum; j++) {
                        Cell cell = row.getCell(j);
                        if (cell != null) {
                            cell.setCellType(CellType.STRING);
                            sb.append(cell.getStringCellValue() + "---");
                        } else {
                            sb.append(" ---");
                        }
                    }
                }
                rows.add(sb.substring(0, sb.length() - 3));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return rows;
    }

    /**
     * 写出到Excel
     *
     * @param dataList
     * @return
     */
    public Workbook writeExcel(List<String> dataList) {
        // 创建工作簿
        Workbook workBook = new XSSFWorkbook();
        try {
            // 创建sheet
            Sheet sheet = workBook.createSheet("导出");
            // 定义Excel数据格式
            ArrayList<Integer> fieldnum = new ArrayList<>();
            for (int j = 0; j < dataList.size(); j++) {
                Row row = sheet.createRow(j);
                String[] fields = dataList.get(j).split("---");
                int length = fields.length;
                fieldnum.add(length);
                for (int k = 0; k < length; k++) {
                    row.createCell(k).setCellValue(fields[k]);
                }
            }
            columnNum = Collections.max(fieldnum);
            // 自动调整列宽
            for (int i = 0; i < columnNum; i++) {
                sheet.autoSizeColumn(i);
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 10 / 10);
            }
            // 隐藏列
            sheet.setColumnHidden(0, true);
            sheet.setColumnHidden(2, true);
            sheet.setColumnHidden(5, true);
            sheet.setColumnHidden(8, true);
            // 冻结首行
            sheet.createFreezePane(0, 1, 0, 1);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workBook;
    }

    /**
     * 判断Excel的版本,获取Workbook
     *
     * @return
     * @throws IOException
     */
    public Workbook getWorkbook(File file) throws IOException {
        Workbook wb = null;
        FileInputStream in = new FileInputStream(file);
        if (file.getName().endsWith("xls")) { // Excel&nbsp;2003
            wb = new HSSFWorkbook(in);
        } else if (file.getName().endsWith("xlsx")) { // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    @RequestMapping("/truncateTable")
    @ResponseBody
    public int truncateTable(HttpServletRequest request) {
        int result = 0;
        String dbkey = request.getParameter("dbkey");
        List<CfgTable> tableList = cfgTableService.getListbyConn(dbkey);
        for (CfgTable cfgTable : tableList) {
            String dtConfigId = cfgTable.getDtConfigId();
            if (dtConfigId.contains("_VIEW")) {
                continue;
            }
            cfgTableService.deleteTableInfo(dtConfigId);
        }
        result = 1;
        return result;
    }

    /**
     * @return 修改结果
     * @throws @Title: updatedtTable @Description: TODO(修改表配置信息)
     * @author guoyanfei
     */
    @RequestMapping("/update")
    @ResponseBody
    public int updatedtTable(
            @RequestBody CfgTable cfgTable, HttpServletRequest request, HttpServletResponse response) {
        int result = 0;
        try {
            result = cfgTableService.updatecfgTable(cfgTable);
            redisCacheComponent.freshCfgTableRedisCache(cfgTable.getDtConn());
            redisCacheDataBase.refreshTreelistRedisCache();
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }

        return result;
    }

    /**
     * @return CfgTable(表配置实体)
     * @throws @Title: getInfoBydtConfigId @Description: TODO(通过表标识查询表配置信息)
     * @author guoyanfei
     */
    @RequestMapping("/getCfgTable")
    @ResponseBody
    public CfgTable getInfoBydtConfigId(HttpServletRequest request, HttpServletResponse response) {
        CfgTable cfgTable = new CfgTable();
        try {
            String dtConfigId = request.getParameter("dtConfigId");
            cfgTable = cfgTableService.getInfoByDtconfigId(dtConfigId);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }

        return cfgTable;
    }

    /**
     * @return List<CfgField>(字段配置表集合)
     * @throws @Title: getFieldBydtConfigId @Description: TODO(通过表标识查询数据中存在字段信息)
     * @author guoyanfei
     */
    @RequestMapping("/getAllField")
    @ResponseBody
    public List<CfgField> getFieldBydtConfigId(
            HttpServletRequest request, HttpServletResponse response) {
        List<CfgField> cfgFields = new ArrayList<CfgField>();
        try {
            String dtConfigId = request.getParameter("dtConfigId");
            cfgFields = redisCacheComponent.cfgFieldRedisCache(dtConfigId);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }
        return cfgFields;
    }

    @PostMapping("/saveCustomFilterField")
    @ResponseBody
    public JsonResult saveCustomFilterField(@RequestBody DataConfigControllerDto dataConfigControllerDto) {
        try {
            cfgFieldService.saveCustomFilterField(dataConfigControllerDto.getCfgFieldCustomFilters(),dataConfigControllerDto.getDtConfigId());
            return JsonResult.success(true);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
            return JsonResult.error(e.getMessage());
        }
    }
    @GetMapping("/getOptor")
    @ResponseBody
    public JsonResult getOptor() {
        List<String> list = new ArrayList<String>();
        list.add("=");
        list.add("!=");
        list.add(">");
        list.add(">=");
        list.add("<");
        list.add("<=");
        list.add("like");
        return JsonResult.success(list);
    }

    @GetMapping("/getCustomFilterField")
    @ResponseBody
    public JsonResult getCustomFilterField(String dtConfigId) {
        try {
            List<CfgFieldCustomFilter> list = cfgFieldService.getCustomFilterField(dtConfigId);
            return JsonResult.success(list);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
            return JsonResult.error(e.getMessage());
        }
    }

    /**
     * @return List<KeyValueModelVo>(keyValue集合)
     * @throws @Title: getNewFieldByTableName @Description: TODO(通过表标识查询元数据中存在字段信息 ， 不包含数据已存在的)
     * @author guoyanfei
     */
    @RequestMapping("/getNewField")
    @ResponseBody
    public List<KeyValueModelVo> getNewFieldByTableName(String dtConfigId) {
        //            HttpServletRequest request) {
        List<KeyValueModelVo> list = new ArrayList<KeyValueModelVo>();
        try {
            //            String dtConfigId = request.getParameter("dtConfigId");
            CfgTable cfgTable = cfgTableService.getInfoByDtconfigId(dtConfigId);

            if (cfgTable != null) {
                String dtname = cfgTable.getDtName();
                String dbkey = cfgTable.getDtConn();
                CfgDb cfgDb = cfgDbService.selectByKey(dbkey);
                list = dataBaseMetadataService.getAllFieldByTableName(cfgDb, dtname);
                List<CfgField> fieldlist = cfgFieldService.getAllField(dtConfigId);

                for (CfgField cfgField : fieldlist) {
                    for (KeyValueModelVo keyvalue : list) {
                        if (keyvalue.getKey().equals(cfgField.getDfName())) {
                            list.remove(keyvalue);
                            break;
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }

        return list;
    }


    // here is logic get same logic
    // return data structure same with update logic
    // add new Field List
    @RequestMapping("/getNewFieldList")
    @ResponseBody
    public List<CfgField> getNewFieldListByTableName(String dtConfigId) {
        //            HttpServletRequest request) {
        List<KeyValueModelVo> keyValueList = new ArrayList<KeyValueModelVo>();
        List<CfgField> cfgFieldList = new ArrayList<>();
        try {
            //            String dtConfigId = request.getParameter("dtConfigId");
            CfgTable cfgTable = cfgTableService.getInfoByDtconfigId(dtConfigId);

            if (cfgTable == null)
                return cfgFieldList;

            String dtname = cfgTable.getDtName();
            String dbkey = cfgTable.getDtConn();
            CfgDb cfgDb = cfgDbService.selectByKey(dbkey);
            keyValueList = dataBaseMetadataService.getAllFieldByTableName(cfgDb, dtname);
            List<CfgField> fieldlist = cfgFieldService.getAllField(dtConfigId);

            for (CfgField cfgField : fieldlist) {
                for (KeyValueModelVo keyvalue : keyValueList) {
                    if (keyvalue.getKey().equals(cfgField.getDfName())) {
                        keyValueList.remove(keyvalue);
                        break;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }

        keyValueList.forEach(keyValueModelVo -> {
            CfgField cfgField = new CfgField();

            cfgField.setDtConfigId(dtConfigId);
            cfgField.setDfName(keyValueModelVo.getKey());
            cfgField.setDfNameCn(keyValueModelVo.getValue());
            cfgField.setDtType(keyValueModelVo.getDataType());
            cfgFieldList.add(cfgField);

        });
        // here keyValueList convert into fieldList
        return cfgFieldList;
    }


    /**
     * @return
     * @throws @Title: batchAddField @Description: TODO(批量新增字段信息)
     * @author guoyanfei
     */
    @RequestMapping("/addField")
    @ResponseBody
    public JsonResult<?> batchField(String dtConfigId, String strFields, String action, boolean isImport) {
        try {
            cfgFieldService.batchAddFields(dtConfigId, strFields, action, isImport);
            return JsonResult.success(true);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
            return JsonResult.success(false);
        }
    }

    // here is change field List Object
    @RequestMapping("/addFieldList")
    @ResponseBody
    public JsonResult<?> batchFieldList(@RequestBody List<CfgField> cfgFieldList) {
        try {
            cfgFieldService.addFieldList(cfgFieldList);
            return JsonResult.success(true);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
            return JsonResult.success(false);
        }
    }


    /**
     * @return List<CfgField>(字段配置表集合)
     * @throws @Title: getFieldBydtConfigId @Description: TODO(通过表标识查询数据中存在字段信息)
     * @author guoyanfei
     */
    @RequestMapping("/getAllViewField")
    @ResponseBody
    public List<CfgField> getViewFieldBydtConfigId(
            HttpServletRequest request, HttpServletResponse response) {
        List<CfgField> cfgFields = new ArrayList<CfgField>();
        try {
            String dtConfigId = request.getParameter("dtConfigId");
            cfgFields = redisCacheComponent.cfgFieldRedisCache(dtConfigId);

            // 设置字段枚举
            List<BaseFieldenum> baseFieldenums = baseFieldenumService.getBaseFieldenumList(dtConfigId);
            if (baseFieldenums != null && baseFieldenums.size() > 0) {
                for (BaseFieldenum baseFieldenum : baseFieldenums) {
                    CfgField cfgField = new CfgField();
                    cfgField.setGuid(baseFieldenum.getGuid());
                    cfgField.setDfName(baseFieldenum.getEnumcode());
                    cfgField.setDfNameCn(baseFieldenum.getEnumname());
                    cfgFields.add(cfgField);
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }

        return cfgFields;
    }

    /**
     * @return PageResponseVo<Map < String, Object>>(分页显示bootstrap Table数据)
     * @throws @Title: getTableViewData @Description: TODO(预览分页查询配置好的元数据信息)
     * @author guoyanfei
     */
    @PostMapping("/viewData")
    @ResponseBody
    public JSONObject getTableViewData(@RequestBody QueryDto queryDto) {
        String pageNo1 = queryDto.getPageNo();
        String pageSize1 = queryDto.getPageSize();
        if (pageNo1==null)pageNo1="1";
        if (pageSize1==null)pageSize1="10";
        int pageNo = Integer.parseInt(pageNo1);
        int pageSize = Integer.parseInt(pageSize1);
        int BeginIndex = (pageNo - 1) * pageSize + 1;
        int EndIndex = pageNo * pageSize;

        String dtConfigId = queryDto.getDtConfigId();
        StringBuilder sqltxt = new StringBuilder();
        PageResponseVo<Map<String, Object>> responseVo = new PageResponseVo<Map<String, Object>>();
        CfgTable cfgTable = cfgTableService.getInfoByDtconfigId(dtConfigId);
        Integer tablecount = 0;
        boolean queryFlag = true;
        if (!ObjectUtils.isEmpty(cfgTable) && !ObjectUtils.isEmpty(cfgTable.getDtName())) {
            String tableName = cfgTable.getDtName(); // 主表名称
            String fields = "";
            String sort = "";
//            StringBuilder tabletxt = new StringBuilder(); // 多表关联
            String tabletxt = "";
            StringBuilder fieldtxt = new StringBuilder(); // 显示列
            CfgDb cfgDb = cfgDbService.getDbInfoBydtconfigID(dtConfigId);
            // 设置表名称
//            tabletxt.append(
//                    StringUtils.isNotEmpty(cfgDb.getDbName())
//                            ? cfgDb.getDbName() + "." + tableName
//                            : tableName);
            tabletxt = StringUtils.isNotEmpty(cfgDb.getDbSchema())
                    ? cfgDb.getDbSchema() + "." + tableName
                    : tableName;
            List<CfgField> fieldlist = redisCacheComponent.cfgFieldRedisCache(dtConfigId);
            // 设置显示字段
            String fieldFormat = "%s";
            switch (cfgDb.getDbVersion()) {
                case "ORACLE":
                case "MongoDB":
                case "DM8":
                case "POSTGRESQL":
                    fieldFormat = "\"%s\"";
                    break;
                case "SQLSERVER":
                    fieldFormat = "[%s]";
                    break;
                case "MYSQL":
                case "KUDU":
                    fieldFormat = "`%s`";
                    break;
                default:
            }
            for (CfgField cfgField : fieldlist) {
                fieldtxt.append(String.format(fieldFormat, cfgField.getDfName())).append(",");
            }
            List<BaseFieldenum> baseFieldenums = baseFieldenumService.getBaseFieldenumList(dtConfigId);
            // 设置字段枚举
            if (baseFieldenums != null && baseFieldenums.size() > 0) {
                for (BaseFieldenum baseFieldenum : baseFieldenums) {
                    if (baseFieldenum.getEnumtype() == 0) {
                        // 自定义JSON
                        fieldtxt.append("(CASE ").append(baseFieldenum.getFieldname()).append(" ");
                        Map<String, String> enumMap =
                                (Map<String, String>) JSON.parse(baseFieldenum.getEnumvalue().toString());
                        for (String key : enumMap.keySet()) {
                            String value = enumMap.get(key).toString();
                            fieldtxt.append(" WHEN '").append(key).append("' THEN '").append(value).append("' ");
                        }
                        fieldtxt.append(" else '' end )\"").append(baseFieldenum.getEnumcode()).append("\",");
                    } else if (baseFieldenum.getEnumtype() == 1) {
                        // SQL语句
                        fieldtxt.append("(select enum_value from(");
                        fieldtxt.append(baseFieldenum.getEnumvalue());
                        fieldtxt.append(") enum where enum.enum_key=").append(baseFieldenum.getFieldname()).append(") ").append(baseFieldenum.getEnumcode()).append(",");
                    }
                }
            }
            fields = fieldtxt.toString();
            fields = fields.substring(0, fields.length() - 1); // for remove final comma
            // 设置排序字段
            tablecount = cfgTable.getDataQuantity();
            if (cfgTable.getDtOrder() != null && !cfgTable.getDtOrder().equals("")) {
                sort = " order by " + cfgTable.getDtOrder();
            }

            // 拼接查询sql数据库包括(ORACLE,MYSQL,SQLSERVER)
            if (cfgDb.getDbVersion().equals("ORACLE")) {
                sqltxt.append("SELECT * ");
                sqltxt.append("FROM (");
                sqltxt.append("SELECT A.*, ROWNUM RN FROM (SELECT " + fields + " FROM ");
                sqltxt.append(tabletxt + " " + sort);
                sqltxt.append(")A WHERE ROWNUM <=" + EndIndex + ") WHERE RN >=" + BeginIndex + "");
            } else if (cfgDb.getDbVersion().equals("MYSQL")
                    || cfgDb.getDbVersion().equals("KingbaseV8")
                    || cfgDb.getDbVersion().equals("DM8")
                    || cfgDb.getDbVersion().equals("MongoDB")) {
                sqltxt.append(" SELECT ");
                sqltxt.append(fields + " ");
                sqltxt.append(" FROM " + tabletxt + " " + sort);
                sqltxt.append(" limit ");
                sqltxt.append(BeginIndex - 1 + "," + pageSize);
            } else if (cfgDb.getDbVersion().equals("SQLSERVER")) {
                sqltxt.append("select * from( ");
                sqltxt.append("select ROW_NUMBER()over(" + sort + ")RN,");
                sqltxt.append(fields + " from " + tabletxt + " )a ");
                sqltxt.append("WHERE RN Between " + BeginIndex + " and " + EndIndex);
            } else if (cfgDb.getDbVersion().equals("POSTGRESQL")) {
                sqltxt.append(" SELECT ");
                sqltxt.append(fields + " ");
                sqltxt.append(" FROM " + tableName + " " + sort);
                sqltxt.append(" limit ");
                sqltxt.append(pageSize + " offset " + (BeginIndex - 1));
            } else if (cfgDb.getDbVersion().equals("KUDU")) {
                if (tablecount != null && tablecount == 0) {
                    queryFlag = false;
                } else {
                    String[] split = sort.split(",");
                    if (split.length > 1) {
                        sort = " order by " + split[1];
                    }
                    String tbtxt = tabletxt.toString();
                    if (BeginIndex < 100) {
                        sqltxt.append(" select t.* from( SELECT ");
                        sqltxt.append(fields + " ");
                        sqltxt.append(" FROM " + tbtxt + " limit 100)t " + sort + " DESC ");
                        sqltxt.append("limit " + pageSize + " offset " + (BeginIndex - 1));
                    } else {
                        sqltxt.append(" SELECT ");
                        sqltxt.append(fields + " ");
                        sqltxt.append(" FROM " + tbtxt + " " + sort + " DESC");
                        sqltxt.append(" limit ");
                        sqltxt.append(pageSize + " offset " + (BeginIndex - 1));
                    }
                }
            }
            String where = "";
            // todo 添加过滤条件 处理不同类型数据库
            List<CfgFieldCustomFilter> cfgFieldCustomFilters = queryDto.getCfgFieldCustomFilters();
            if (cfgFieldCustomFilters!=null) {
                where+=" where 1=1 ";
                for (CfgFieldCustomFilter item : cfgFieldCustomFilters) {
                    String optor = item.getOptor();
                    String searchV = item.getSearchV();
                    String dfName = item.getDfName();
                    if (searchV!=null&&!searchV.isEmpty()) {
                        if ("<".equals(optor)) {
                            optor = "&lt;";
                        }
                        if ("<=".equals(optor)) {
                            optor = "&lt;=";
                        }
                        if ("like".equals(optor)) {
                            searchV = "%" + searchV + "%";
                        }
                        where += " and " + dfName + " " + optor + " '" + searchV +"' ";
                    }
                }
            }
            String finalSql=sqltxt.toString();
            if (!where.isEmpty()){
                finalSql=sqltxt.toString().replace(tableName ,tableName+" "+where);
            }

            List<Map<String, Object>> rows = null;
            if (queryFlag) {
                rows = dataBaseMetadataService.getSqlResult(cfgDb, finalSql);
            }

            // 查询总条数
            String sqlcount = "";
            if ("SQLSERVER".equals(cfgDb.getDbVersion()) || "POSTGRESQL".equals(cfgDb.getDbVersion())) {
                sqlcount = "select count(*) NUM from " + tabletxt +" " + where;
            } else {
                sqlcount = "select count(*) NUM from " + tabletxt +" " + where;
            }

            int totalNum = 0;
            if (cfgDb.getDbVersion().equals("MongoDB")) {
                MongoDBUtil instance = MongoDBUtil.getInstance();
                QueryInfo queryInfo = new QueryInfo();
                queryInfo.setCollectionName(tableName);
                queryInfo.setDb(instance.createConnection(cfgDb.getDbUrl()));
                queryInfo.setWhere(new BasicDBObject());
                Long count = instance.findCount(queryInfo);
                totalNum = Integer.parseInt(count.toString());
                instance.close();
            } else if (cfgDb.getDbVersion().equals("KUDU")) {
                totalNum = tablecount != null ? tablecount : 0;
            } else {
                List<Map<String, Object>> total = dataBaseMetadataService.getSqlResult(cfgDb, sqlcount);
                List<Map<String, Object>> maps = mapKeysToUpperCaseObject(total);
                totalNum = Integer.parseInt(maps.get(0).get("NUM").toString());
            }

            // 处理 oracle.sql.TIMESTAMP报错
            if (!CollectionUtils.isEmpty(rows)) {
                for (Map<String, Object> row : rows) {
                    if (row == null || row.size() == 0) continue;
                    for (String columnName : row.keySet()) {
                        Object columnValue = row.get(columnName);
                        if (columnValue == null) continue;

                        if ("oracle.sql.TIMESTAMP".equals(columnValue.getClass().getName())) {
                            try {
                                Class clz = columnValue.getClass();
                                Method m = clz.getMethod("timestampValue");
                                java.sql.Timestamp t1 = (java.sql.Timestamp) m.invoke(columnValue);
                                row.put(columnName, t1);
                            } catch (Exception e) {
                                logger.error("处理 oracle.sql.TIMESTAMP报错问题异常", e);
                            }
                        } else {
                            row.put(columnName, columnValue);
                        }
                    }
                }
            }
            responseVo.setRows(rows);
            responseVo.setTotal(totalNum);
        }
        String jsonString = JSON.toJSONString(responseVo);
        JSONObject jsonObject = JSON.parseObject(jsonString);
        return jsonObject;
    }

    /**
     * @return
     * @throws @Title: delFieldByGuid @Description: TODO(批量删除字段信息)
     * @author guoyanfei
     */
    @RequestMapping("/delField")
    @ResponseBody
    public void delFieldByGuid(HttpServletRequest requset) {
        try {
            String guids = requset.getParameter("guids");
            String[] guidarray = guids.split(",");
            List<String> guidList = Arrays.asList(guidarray);
            cfgFieldService.deleteBatchByGuid(guidList);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }
    }

    /**
     * @return 修改结果
     * @throws @Title: updateFieldInfo @Description: TODO(修改字段信息)
     * @author guoyanfei
     */
    @RequestMapping("/updateField")
    @ResponseBody
    public int updateFieldInfo(@RequestBody CfgField cfgField) {
        int result = 0;
        try {
            result = cfgFieldService.updateFieldInfo(cfgField);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }
        return result;
    }

    @RequestMapping("/updateFieldList")
    @ResponseBody
    public int updateFieldListInfo(@RequestBody List<CfgField> cfgFieldList) {
        int result = 0;
        try {
            result = cfgFieldService.updateFieldInfoList(cfgFieldList);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }
        return result;
    }


    /**
     * @return 修改结果
     * @throws @Title: deleteTableBydtConfigId @Description: TODO(通过表标识删除配置表信息)
     * @author guoyanfei
     */
    @RequestMapping("/delTableInfo")
    @ResponseBody
    public int deleteTableBydtConfigId(HttpServletRequest request) {
        int result = 0;
        try {
            String dtConfigId = request.getParameter("dtConfigId");
            result = cfgTableService.deleteTableInfo(dtConfigId);
            // 刷新Tree缓存
            redisCacheDataBase.refreshTreelistRedisCache();
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }

        return result;
    }

    /**
     * @return List<CfgTable>集合
     * @throws @Title: getFieldbydbkeyandtablename @Description: TODO(通过数据库主键和表名称查询该表所有字段信息)
     * @author guoyanfei
     */
    @RequestMapping("/getFieldbydbkeyandtablename")
    @ResponseBody
    public List<CfgField> getFieldbydbkeyandtablename(HttpServletRequest request) {
        List<CfgField> cfgFields = new ArrayList<CfgField>();
        try {
            String dbkey = request.getParameter("dbkey");
            String tablename = request.getParameter("tablename");
            cfgFields = baseFieldconfigService.selectFieldBydbkeyAndtable(dbkey, tablename);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }

        return cfgFields;
    }

    /**
     * @return List<CfgField>集合 @Title: selectFieldNotInFieldConfig @Desciption:TODO(
     * 通过表标识查询在其他配置表中不存在的字段信息)
     * @author guoyanfei
     */
    @RequestMapping("/selectFieldNotInFieldConfig")
    @ResponseBody
    public List<CfgField> selectFieldNotInFieldConfig(HttpServletRequest request) {
        List<CfgField> cfgFields = new ArrayList<CfgField>();
        try {
            String dtconfigid = request.getParameter("dtConfigId");
            cfgFields = cfgFieldService.selectFieldNotInFieldConfig(dtconfigid);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
        }

        return cfgFields;
    }

    /**
     * @return BaseFieldenum集合(json格式)
     * @description 通过表标识查询字段枚举信息列表
     * @author guoyanfei
     * @date 2017/11/28
     */
    @RequestMapping("/getBaseFieldenumList")
    @ResponseBody
    public List<BaseFieldenum> getBaseFieldenumList(HttpServletRequest request) {
        List<BaseFieldenum> baseFieldenums = new ArrayList<BaseFieldenum>();
        try {
            String dtConfigId = request.getParameter("dtConfigId");
            baseFieldenums = baseFieldenumService.getBaseFieldenumList(dtConfigId);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return baseFieldenums;
    }

    /**
     * @return 添加结果
     * @description 添加字段枚举信息
     * @author guoyanfei
     * @date 2017/11/28
     */
    @RequestMapping("/addBaseFieldenum")
    @ResponseBody
    public Integer addBaseFieldenum(@RequestBody BaseFieldenum baseFieldenum) {
        Integer result = 0;
        try {
            result = baseFieldenumService.insertBatchBaseFieldenum(baseFieldenum);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return result;
    }

    /**
     * @return 修改结果
     * @description 修改字段枚举信息
     * @author guoyanfei
     * @date 2017/11/28
     */
    @RequestMapping("/updateBaseFieldenum")
    @ResponseBody
    public Integer updateBaseFieldenum(@RequestBody BaseFieldenum baseFieldenum) {
        Integer result = 0;
        try {
            result = baseFieldenumService.updateBaseFieldenum(baseFieldenum);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return result;
    }

    /**
     * @return 删除结果
     * @description 批量删除字段枚举信息
     * @author guoyanfei
     * @date 2017/11/28
     */
    @RequestMapping("/deleteBaseFieldenum")
    @ResponseBody
    public Integer deleteBaseFieldenum(HttpServletRequest request) {
        Integer result = 0;
        try {
            String guids = request.getParameter("guids");
            result = baseFieldenumService.deleteBatchBaseFieldenum(guids);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return result;
    }

    /**
     * @return CfgField集合
     * @description 查询没有设置为枚举的字段信息
     * @author guoyanfei
     * @date 2017/11/28
     */
    @RequestMapping("/getFieldNotInFieldenum")
    @ResponseBody
    public List<CfgField> getFieldNotInFieldenum(HttpServletRequest request) {
        List<CfgField> cfgFields = new ArrayList<CfgField>();
        try {
            String dtConfigId = request.getParameter("dtConfigId");
            cfgFields = cfgFieldService.getAllField(dtConfigId);

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return cfgFields;
    }

    /**
     * @return BaseFieldenum实体
     * @description 通过guid查询字段枚举信息
     * @author guoyanfei
     * @date 2017/11/28
     */
    @RequestMapping("/getFieldEnumByguid")
    @ResponseBody
    public BaseFieldenum getFieldEnumByguid(HttpServletRequest request) {
        BaseFieldenum baseFieldenum = new BaseFieldenum();
        try {
            String guid = request.getParameter("guid");
            baseFieldenum = baseFieldenumService.getBaseFieldenumByguid(guid);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }

        return baseFieldenum;
    }

    /**
     * @return true或false @Description 测试数据源是否联通
     * @author guoyanfei
     * @date 2018/01/16
     */
    @RequestMapping("/testDataConnection")
    @ResponseBody
    public Boolean testDataConnection(HttpServletRequest request) {
        Boolean result = false;
        try {
            String dbKey = request.getParameter("dbKey");
            CfgDb cfgdb = cfgDbService.selectByKey(dbKey);
            if (cfgdb != null) {
                result = dataBaseConnInfo.testConnection(cfgdb);
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return result;
    }

    /**
     * * show 查询所有数据库连接配置信息 create by guoyanfei on 2018/12/25
     *
     * @return CfgDb实体List集合
     */
    @RequestMapping("/getCfgdbList")
    @ResponseBody
    public List<CfgDb> getCfgdbList() {
        List<CfgDb> cfgDbList = cfgDbService.getCfgDBList();
        return cfgDbList;
    }

    /**
     * show 通过dbkey分页查询配置表信息 create by guoyanfei on 2018/12/25
     *
     * @param request
     * @return
     */
    @RequestMapping("/selectCfgtableList")
    @ResponseBody
    public PageResponseVo selectCfgtableList(HttpServletRequest request) {
        PageResponseVo pageResponseVo = new PageResponseVo();
        try {
            Integer pageNo = Integer.valueOf(request.getParameter("pageNo"));
            Integer pageSize = Integer.valueOf(request.getParameter("pageSize"));
            String dbkey = request.getParameter("dbkey");
            String paramtxt = request.getParameter("paramtxt");
            String tableType = request.getParameter("tableType");
            pageResponseVo =
                    cfgTableService.getCfgTablebyPage(dbkey, paramtxt, pageNo, pageSize, Integer.valueOf(tableType));

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return pageResponseVo;
    }

    @RequestMapping("/selectTreeCfgtableList")
    @ResponseBody
    public PageResponseVo selectTreeCfgtableList(HttpServletRequest request) {
        PageResponseVo pageResponseVo = new PageResponseVo();
        try {
            Integer pageNo = Integer.valueOf(request.getParameter("pageNo"));
            Integer pageSize = Integer.valueOf(request.getParameter("pageSize"));
            String id = request.getParameter("id");
            String paramtxt = request.getParameter("paramtxt");
            String contentType = "2";
            if (id.length() < 4) {
                contentType = "1";
            }
            pageResponseVo =
                    cfgTableService.getCfgTableTreebyPage(id, paramtxt, pageNo, pageSize, contentType);

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return pageResponseVo;
    }

    @RequestMapping("/getRecourdTotalById")
    @ResponseBody
    public Integer getRecourdTotalById(HttpServletRequest request) {
        String id = request.getParameter("id");
        String contentType = "2";
        if (id.length() < 4) {
            contentType = "1";
        }
        Integer recourdTotal = cfgTableService.getRecourdTotalBydbkey(id, "", contentType);
        recourdTotal = recourdTotal != null ? recourdTotal : 0;
        return recourdTotal;
    }
}
